package com.obrock;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import java.io.ByteArrayOutputStream;
import java.io.DataOutputStream;
import java.io.IOException;
import java.util.*;

/**
 * @author Дмитрий Г. Кузнецов
 */
public class Database extends SQLiteOpenHelper {

    static final String DB_NAME = "ob-rock.db";
    static final int DB_VERSION = 88;

    static final int OPERATION_INSERT = 0;
    static final int OPERATION_UPDATE = 1;
    static final int OPERATION_DELETE = 2;
    static final int OPERATION_EXECUTE = 3;

    Context context;

    public Database(Context context) {
        super(context, DB_NAME, null, DB_VERSION);
        this.context = context;
    }

    void createTables(SQLiteDatabase db) {
        Log.d(Main.LOG_TAG, "Creating all tables for " + DB_NAME);
        Log.d(Main.LOG_TAG, "Creating table parameters");

        db.execSQL("CREATE TABLE uuid (" +
                "rowid INTEGER PRIMARY KEY AUTOINCREMENT," +
                "name TEXT," +
                "ident INTEGER" +
                ");");

        db.execSQL("INSERT INTO uuid (name,ident) VALUES ('enable',1)");


        /**
         * Параметры программы, локальные
         */
        db.execSQL("CREATE TABLE parameters ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "name TEXT,"
                + "value_int INTEGER,"
                + "value_text TEXT,"
                + "value_float DOUBLE"
                + ");");

        /*
        db.execSQL("INSERT INTO parameters (name, value_int) " +
                "VALUES (?,?)",
                new Object[]{"SYNC_PERIOD", 600000});
        db.execSQL("INSERT INTO parameters (name, value_int) " +
                "VALUES (?,?)",
                new Object[]{"SYNC_ENABLE", 1});
        db.execSQL("INSERT INTO parameters (name, value_int) " +
                "VALUES (?,?)",
                new Object[]{"SMS_ENABLE", 1});
        db.execSQL("INSERT INTO parameters (name, value_int) " +
                "VALUES (?,?)",
                new Object[]{"PROGNOSIS_DAYS_AFTER", 10});
        */

        createTriggers(db, "parameters");

        /**
         * Таблица счетов
         */
        Log.d(Main.LOG_TAG, "Creating table accounts");
        db.execSQL("CREATE TABLE accounts ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "name TEXT,"
                + "type INTEGER,"
                + "date DATE,"
                + "amount REAL,"
                + "remark TEXT,"
                + "flags INTEGER,"
                + "bank_name TEXT,"
                + "bank_bik TEXT,"
                + "bank_corr TEXT,"
                + "bank_account TEXT,"
                + "bank_inn TEXT,"
                + "bank_kpp TEXT,"
                + "bank_receiver TEXT,"
                + "bank_purpose TEXT,"
                + "card_type INTEGER,"
                + "card_number TEXT,"
                + "card_expire DATE,"
                + "card_name TEXT,"
                + "card_pin TEXT,"
                + "card_cvc TEXT "
                + ");");

        createTriggers(db, "accounts");

        /**
         * Таблица проводок
         */
        Log.d(Main.LOG_TAG, "Creating table balance");
        db.execSQL("CREATE TABLE balance ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "id_parent INTEGER,"
                + "name TEXT,"
                + "type INTEGER,"
                + "date DATE,"
                + "id_from INTEGER,"
                + "id_to INTEGER,"
                + "amount REAL,"
                + "mode INTEGER, "
                + "flags INTEGER,"
                + "period INTEGER,"
                + "number TEXT,"
                + "template TEXT,"
                + "key TEXT,"
                + "remark TEXT "
                + ");");

        createTriggers(db, "balance");


        Log.d(Main.LOG_TAG, "Creating table complex");
        db.execSQL("CREATE TABLE complex ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "id_balance INTEGER,"
                + "id_account INTEGER,"
                + "id_ref INTEGER,"
                + "type INTEGER,"
                + "name TEXT,"
                + "amount REAL "
                + ");");

        createTriggers(db, "complex");


        /**
         * Иерархическая аналитика, субконто -> значения
         */
        Log.d(Main.LOG_TAG, "Creating table analytics");
        db.execSQL("CREATE TABLE analytics ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "id_parent INTEGER,"
                + "name TEXT,"
                + "remark TEXT "
                + ");");

        createTriggers(db, "analytics");


        /**
         * Связь субконто со счетами
         */
        Log.d(Main.LOG_TAG, "Creating table relates");
        db.execSQL("CREATE TABLE relates ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "id_account INTEGER,"
                + "id_analytics INTEGER "
                + ");");

        createTriggers(db, "relates");


        /**
         * Значения субконто для проводок
         */
        Log.d(Main.LOG_TAG, "Creating table links");
        db.execSQL("CREATE TABLE links ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "type INTEGER, "
                + "id_balance INTEGER,"
                + "id_analytics INTEGER "
                + ");");

        createTriggers(db, "links");


        Log.d(Main.LOG_TAG, "Creating table sync");
        db.execSQL("CREATE TABLE sync ("
                + "rowid INTEGER PRIMARY KEY AUTOINCREMENT,"
                + "id INTEGER,"
                + "name TEXT,"
                + "operation INTEGER,"
                + "id_record INTEGER,"
                + "date DATETIME "
                + ");");

        db.execSQL("INSERT INTO uuid (name) VALUES ('sync');");

        //

    }

    void uuidEnable(boolean on) {
        if (on) {
            this.getWritableDatabase().execSQL("UPDATE uuid SET ident = 1 WHERE name = 'enable'");
        } else {
            this.getWritableDatabase().execSQL("UPDATE uuid SET ident = 0 WHERE name = 'enable'");
        }
    }

    void createTriggers(SQLiteDatabase db, String table) {
        db.execSQL("INSERT INTO uuid (name) VALUES ('" + table + "');");

        db.execSQL("CREATE TRIGGER insert_" + table + " AFTER INSERT ON " + table + " " +
                "BEGIN " +
                "   UPDATE uuid SET ident = (SELECT " +
                "       CASE ident " +
                "           WHEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "           THEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) + 1 " +
                "           ELSE CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "       END " +
                "       FROM uuid WHERE name = '" + table + "') " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND name = '" + table + "'; " +
                "" +
                "  UPDATE " + table + " SET id = (SELECT ident FROM uuid WHERE name = '" + table + "') " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND rowid = NEW.rowid; " +
                "" +
                "   UPDATE uuid SET ident = (SELECT " +
                "       CASE ident " +
                "           WHEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "           THEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) + 1 " +
                "           ELSE CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "       END " +
                "       FROM uuid WHERE name = 'sync') " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND name = 'sync'; " +
                "" +
                "  INSERT INTO sync (id, id_record, name, operation) " +
                "   SELECT ident, (SELECT ident FROM uuid WHERE name = '" + table + "'), '" + table + "', 0 " +
                "   FROM uuid " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND name = 'sync'; " +
                "END;");

        db.execSQL("CREATE TRIGGER update_" + table + " UPDATE ON " + table + " " +
                "BEGIN " +
                "   UPDATE uuid SET ident = (SELECT " +
                "       CASE ident " +
                "           WHEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "           THEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) + 1 " +
                "           ELSE CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "       END " +
                "       FROM uuid WHERE name = 'sync') " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND name = 'sync'; " +
                "" +
                "  INSERT INTO sync (id, id_record, name, operation) " +
                "   SELECT ident, NEW.id, '" + table + "', 1 " +
                "   FROM uuid " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND NEW.id = OLD.id" +
                "   AND name = 'sync'; " +
                "END;");

        db.execSQL("CREATE TRIGGER delete_" + table + " DELETE ON " + table + " " +
                "BEGIN " +
                "   UPDATE uuid SET ident = (SELECT " +
                "       CASE ident " +
                "           WHEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "           THEN CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) + 1 " +
                "           ELSE CAST((strftime('%s') - strftime('%S') + strftime('%f')) * 1000 AS INTEGER) " +
                "       END " +
                "       FROM uuid WHERE name = 'sync') " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND name = 'sync'; " +
                "" +
                "  INSERT INTO sync (id, id_record, name, operation) " +
                "   SELECT ident, OLD.id, '" + table + "', 2 " +
                "   FROM uuid " +
                "   WHERE (SELECT ident FROM uuid WHERE name='enable') = 1 " +
                "   AND name = 'sync'; " +
                "END;");

    }

    void dropTables(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS parameters");
        db.execSQL("DROP TABLE IF EXISTS accounts");
        db.execSQL("DROP TABLE IF EXISTS balance");
        db.execSQL("DROP TABLE IF EXISTS complex");
        db.execSQL("DROP TABLE IF EXISTS analytics");
        db.execSQL("DROP TABLE IF EXISTS relates");
        db.execSQL("DROP TABLE IF EXISTS links");
        db.execSQL("DROP TABLE IF EXISTS sync");
        db.execSQL("DROP TABLE IF EXISTS uuid");

    }


    public byte[] packRecord(String tableName, long id) throws IOException {
        ByteArrayOutputStream bufStream = new ByteArrayOutputStream();
        DataOutputStream outWriter = new DataOutputStream(bufStream);

        Cursor v = this.getReadableDatabase().rawQuery("SELECT * FROM " + tableName + " WHERE id=?", new String[]{"" + id});
        if (v.moveToFirst()) {

            Cursor cr = this.getReadableDatabase().rawQuery("PRAGMA table_info(" + tableName + ");", null);
            if (cr.moveToFirst()) {

                int i = 0;
                do {
                    String name = cr.getString(1);
                    if (!name.equals("rowid")) {
                        String type = cr.getString(2);

                        outWriter.writeUTF(name);
                        outWriter.writeUTF(type);


                        /*
                        Cursor v = this.getReadableDatabase().rawQuery("SELECT " + name + " FROM " + tableName + " WHERE id=?", new String[]{"" + id});
                        if (v.moveToFirst()) {
                        */
                        if (type.equals("INTEGER")) {
                            outWriter.writeLong(v.getLong(i));
                        } else if (type.equals("REAL")) {
                            outWriter.writeDouble(v.getDouble(i));
                        } else if (type.equals("TEXT")) {
                            String s = v.getString(i);
                            outWriter.writeUTF(s == null ? "" : s);
                        } else if (type.equals("DATE")) {
                            outWriter.writeLong(v.getLong(i));
                        }
                    }
                    ++i;
                    /*
                    }
                    */
                } while (cr.moveToNext());
            }
            cr.close();
            v.close();
            return bufStream.toByteArray();
        }
        v.close();
        return null;
    }


    public List<SyncSeed> getChanges(long index) throws IOException {
        List<SyncSeed> set = new ArrayList<SyncSeed>(0);
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM sync " +
                "WHERE id > ? " +
                "ORDER BY date ", new String[]{"" + index});
        if (cr.moveToFirst()) {
            do {
                SyncSeed c = new SyncSeed(this, cr.getLong(0));
                if (c.recordID != -1) {
                    set.add(c);
                }
            } while (cr.moveToNext());
        }
        return set;
    }

    public List<SyncSeed> getSnapshot(String table) throws IOException {
        List<SyncSeed> set = new ArrayList<SyncSeed>(0);
        set.add(new SyncSeed(this, "DELETE FROM " + table));
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM " + table + " " +
                "ORDER BY id", null);
        if (cr.moveToFirst()) {
            do {
                set.add(new SyncSeed(this, table, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        return set;
    }

    public List<SyncSeed> getSnapshot() throws IOException {
        List<SyncSeed> set = new ArrayList<SyncSeed>(0);

        set.add(new SyncSeed(this, "DELETE FROM sync"));
        set.addAll(getSnapshot("accounts"));
        set.addAll(getSnapshot("balance"));
        set.addAll(getSnapshot("complex"));
        set.addAll(getSnapshot("analytics"));
        set.addAll(getSnapshot("relates"));
        set.addAll(getSnapshot("links"));

        long id = new Date().getTime();
        for (SyncSeed s : set) {
            s.syncID = ++id;
        }

        return set;
    }

    public void updateChanges(List<SyncSeed> changes) {
        this.getWritableDatabase().execSQL("DELETE FROM sync");
        for (SyncSeed s : changes) {
            this.getWritableDatabase().execSQL("INSERT INTO sync " +
                    "(id, name, operation, id_record, date) " +
                    "VALUES (?,?,?,?,?)",
                    new Object[]{s.syncID, s.name, s.operation, s.recordID, s.date});
        }
    }

    public long getLastChangeIndex() {
        long x = 0;
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT COALESCE(MAX(id),0) FROM sync", null);
        if (cr.moveToFirst()) {
            x = cr.getLong(0);
        }
        return x;
    }

    @Override
    public void onOpen(SQLiteDatabase db) {
        garbageCollect(db);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        createTables(db);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.d(Main.LOG_TAG, "Upgrading database from " + oldVersion + " to " + newVersion);
        String query = "";

        if (newVersion < 80) {
            dropTables(db);
            createTables(db);
        } else {

            if (oldVersion <= 81) {
                Log.d(Main.LOG_TAG, "Upgrading database from " + oldVersion + " to 82");
                query = "ALTER TABLE balance ADD COLUMN key TEXT;";
                Log.d(Main.LOG_TAG, query);
                db.execSQL(query);
                oldVersion = 82;
            }
            if (oldVersion <= 82) {
                Log.d(Main.LOG_TAG, "Technical upgrade: " + oldVersion + " to 83");
                query = "DELETE FROM balance WHERE mode=" + Balance.MODE_PROGNOSIS + ";";
                Log.d(Main.LOG_TAG, query);
                db.execSQL(query);
                oldVersion = 83;
            }

            if (oldVersion <= 83) {
                Log.d(Main.LOG_TAG, "Upgrading database from " + oldVersion + " to 84");
                db.execSQL("INSERT INTO parameters (name, value_int) " +
                        "VALUES (?,?)",
                        new Object[]{"SMS_ENABLE", 1});
                oldVersion = 84;
            }

            if (oldVersion <= 84) {
                Log.d(Main.LOG_TAG, "Upgrading database from " + oldVersion + " to 85");
                db.execSQL("INSERT INTO parameters (name, value_int) " +
                        "VALUES (?,?)",
                        new Object[]{"PROGNOSIS_DAYS_AFTER", 10});
                oldVersion = 85;
            }

            if (oldVersion <= 85) {
                db.execSQL("DELETE FROM parameters WHERE name = ?",
                        new Object[]{"SYNC_PERIOD"});
                db.execSQL("DELETE FROM parameters WHERE name = ?",
                        new Object[]{"SYNC_ENABLE"});
                db.execSQL("DELETE FROM parameters WHERE name = ?",
                        new Object[]{"SMS_ENABLE"});
                db.execSQL("DELETE FROM parameters WHERE name = ?",
                        new Object[]{"PROGNOSIS_DAYS_AFTER"});
                oldVersion = 86;
            }

            if (oldVersion <= 86) {
                garbageCollect(db);
                oldVersion = 87;
            }

            if (oldVersion <= 87) {
                Log.d(Main.LOG_TAG, "Upgrading database from " + oldVersion + " to 88");
                query = "ALTER TABLE complex ADD COLUMN id_ref INTEGER DEFAULT -1;";
                Log.d(Main.LOG_TAG, query);
                db.execSQL(query);
                oldVersion = 88;
            }

        }
    }

    public void garbageCollect() {
        garbageCollect(getWritableDatabase());
    }

    private void clearDupes(SQLiteDatabase db, String table) {
        db.execSQL("DELETE FROM " + table + " WHERE "
                + " (SELECT MAX(rowid) FROM " + table + " AS slave "
                + "     WHERE " + table + ".id = slave.id) <> rowid ");
    }

    private void garbageCollect(SQLiteDatabase db) {
        db.execSQL("DELETE FROM complex "
                + "WHERE id_balance IN (" +
                "   SELECT id FROM balance " +
                "   WHERE COALESCE(id_from,-1) = -1 " +
                "   AND COALESCE(id_to,-1) = -1 " +
                "   AND mode = ?)",
                new Object[]{Balance.MODE_NORMAL});

        db.execSQL("DELETE FROM links "
                + "WHERE id_balance IN (" +
                "   SELECT id FROM balance " +
                "   WHERE COALESCE(id_from,-1) = -1 " +
                "   AND COALESCE(id_to,-1) = -1 " +
                "   AND mode = ?)",
                new Object[]{Balance.MODE_NORMAL});

        db.execSQL("DELETE FROM balance " +
                "   WHERE COALESCE(id_from,-1) = -1 " +
                "   AND COALESCE(id_to,-1) = -1 " +
                "   AND mode = ?",
                new Object[]{Balance.MODE_NORMAL});

        db.execSQL("UPDATE uuid SET ident = 0 WHERE name = 'enable'");
        clearDupes(db, "parameters");
        clearDupes(db, "accounts");
        clearDupes(db, "balance");
        clearDupes(db, "complex");
        clearDupes(db, "analytics");
        clearDupes(db, "relates");
        clearDupes(db, "links");
        clearDupes(db, "sync");
        db.execSQL("UPDATE uuid SET ident = 1 WHERE name = 'enable'");
    }

    public String getParamString(String name) {
        String ret = null;
        if (getReadableDatabase() == null) return null;
        Cursor cr = getReadableDatabase().rawQuery("SELECT value_text FROM parameters WHERE name=?", new String[]{name});
        if (cr.moveToFirst()) {
            ret = cr.getString(0);
        }
        cr.close();
        return ret;
    }

    public Long getParamInt(String name) {
        Long ret = null;
        if (getReadableDatabase() == null) return null;
        Cursor cr = getReadableDatabase().rawQuery("SELECT value_int FROM parameters WHERE name=?", new String[]{name});
        if (cr.moveToFirst()) {
            ret = cr.getLong(0);
        }
        cr.close();
        return ret;
    }


    public Double getParamFloat(String name) {
        Double ret = null;
        if (getReadableDatabase() == null) return null;
        Cursor cr = getReadableDatabase().rawQuery("SELECT value_float FROM parameters WHERE name=?", new String[]{name});
        if (cr.moveToFirst()) {
            return cr.getDouble(0);
        }
        cr.close();
        return ret;
    }


    public void setParam(String name, String value) {
        if (getParamString(name) == null) {
            this.getWritableDatabase().execSQL("INSERT INTO parameters (name, value_text) " +
                    "VALUES (?,?)",
                    new Object[]{name, value});
        } else {
            this.getWritableDatabase().execSQL("UPDATE parameters " +
                    "SET value_text = ? " +
                    "WHERE name = ?",
                    new Object[]{value, name});
        }
        DropboxThread.dataChanged();
    }

    public void setParam(String name, long value) {
        if (getParamInt(name) == null) {
            this.getWritableDatabase().execSQL("INSERT INTO parameters (name, value_int) " +
                    "VALUES (?,?)",
                    new Object[]{name, value});
        } else {
            this.getWritableDatabase().execSQL("UPDATE parameters " +
                    "SET value_int = ? " +
                    "WHERE name = ?",
                    new Object[]{value, name});
        }
        DropboxThread.dataChanged();
    }

    public void setParam(String name, Double value) {
        if (getParamFloat(name) == null) {
            this.getWritableDatabase().execSQL("INSERT INTO parameters (name, value_float) " +
                    "VALUES (?,?)",
                    new Object[]{name, value});
        } else {
            this.getWritableDatabase().execSQL("UPDATE parameters " +
                    "SET value_float = ? " +
                    "WHERE name = ?",
                    new Object[]{value, name});
        }
        DropboxThread.dataChanged();
    }

    public void unsetParam(String name) {
        this.getWritableDatabase().execSQL("DELETE FROM parameters WHERE name=?", new String[]{name});
        DropboxThread.dataChanged();
    }

    public Account[] getAccountsList() {
        List<Account> acs = new ArrayList<Account>(0);
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id FROM accounts ORDER BY type, name", null);
        if (cr.moveToFirst()) {
            do {
                acs.add(new Account(this, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        cr.close();
        return acs.toArray(new Account[]{});
    }

    public Analytics[] getAnalyticsList(long parentID) {
        List<Analytics> acs = new ArrayList<Analytics>(0);
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id "
                + "FROM analytics "
                + "WHERE id_parent = ? "
                + "ORDER BY name",
                new String[]{"" + parentID});
        if (cr.moveToFirst()) {
            long p = cr.getLong(1);
            do {
                acs.add(new Analytics(this, null, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        cr.close();
        return acs.toArray(new Analytics[]{});
    }

    public List<Analytics> getAnalyticsList(Account ac) {
        List<Analytics> acs = new ArrayList<Analytics>(0);
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT analytics.id, COALESCE(relates.id,-1) "
                + "FROM analytics "
                + "LEFT JOIN relates "
                + "  ON relates.id_analytics = analytics.id "
                + "  AND relates.id_account = ? "
                + "WHERE id_parent = -1 "
                + "ORDER BY analytics.name",
                new String[]{"" + ac.accountID});
        if (cr.moveToFirst()) {
            do {
                Analytics an = new Analytics(this, null, cr.getLong(0));
                if (cr.getLong(1) != -1) {
                    an.selected = true;
                } else {
                    an.selected = false;
                }
                acs.add(an);
            } while (cr.moveToNext());
        }
        cr.close();
        return acs;
    }

    public Balance[] getTransfers(Account ac) {
        List<Balance> t = new ArrayList<Balance>(0);
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id "
                + "FROM balance "
                + "WHERE mode = ? "
                + " AND (id_from = ? "
                + "OR (type <> ?  AND id_to = ?) "
                + "OR (type = ? AND (SELECT COUNT(*) " +
                "   FROM complex " +
                "   WHERE complex.id_balance = balance.id " +
                "   AND complex.id_account = ?) > 0)) "
                + "ORDER BY date",
                new String[]{"" + Balance.MODE_NORMAL,
                        "" + ac.accountID,
                        "" + Balance.TYPE_COMPLEX,
                        "" + ac.accountID,
                        "" + Balance.TYPE_COMPLEX,
                        "" + ac.accountID});
        if (cr.moveToFirst()) {
            do {
                t.add(new Balance(this, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        cr.close();
        return t.toArray(new Balance[]{});
    }


    public Balance[] getTemplates() {
        List<Balance> g = new ArrayList<Balance>(0);
        Cursor cr;
        cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM balance " +
                "WHERE mode = ? " +
                "ORDER BY name ",
                new String[]{"" + Balance.MODE_TEMPLATE});
        if (cr.moveToFirst()) {
            do {
                g.add(new Balance(this, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        cr.close();
        return g.toArray(new Balance[]{});
    }


    public Balance[] getPrognosis(Calendar[] dates) {
        List<Balance> g = new ArrayList<Balance>(0);
        Cursor cr;
        cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM balance " +
                "WHERE mode = ? " +
                "ORDER BY date ",
                new String[]{"" + Balance.MODE_PROGNOSIS});
        if (cr.moveToFirst()) {
            do {
                g.addAll(new Balance(this, cr.getLong(0)).getGraph(dates));
            } while (cr.moveToNext());
        }
        cr.close();
        Collections.sort(g);
        return g.toArray(new Balance[]{});
    }

    Balance getGraphRecord(Account ac) {
        Balance ret = null;
        Cursor cr;
        cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM balance " +
                "WHERE mode = ? " +
                /*"AND (flags & 255) = ? " +*/
                "AND id_to = ? " +
                "ORDER BY date ",
                new String[]{"" + Balance.MODE_PROGNOSIS,
                        /*"" + Balance.FLAG_PERIOD_CREDIT,*/
                        "" + ac.accountID});
        if (cr.moveToFirst()) {
            ret = new Balance(this, cr.getLong(0));
        }
        cr.close();
        return ret;
    }

    public Long getIdent(String table) {
        Long ret = null;
        try {
            Thread.sleep(100);
        } catch (InterruptedException e) {
            //
        }
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id FROM " + table + " WHERE rowid = last_insert_rowid()", null);
        if (cr.moveToFirst()) {
            ret = new Long(cr.getLong(0));
        }
        cr.close();
        return ret;
    }

    public Long findRemark(String remark) {
        Long id = null;
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM balance " +
                "WHERE (mode = ? OR mode = ?) " +
                "AND remark = ? ",
                new String[]{"" + Balance.MODE_PROGNOSIS, "" + Balance.MODE_NORMAL, remark});
        if (cr.moveToFirst()) {
            id = new Long(cr.getLong(0));
        }
        cr.close();
        return id;
    }

    public Balance getLastSMSTransfer(Account ac) {
        long id = -1;
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT MAX(id) " +
                "FROM balance " +
                "WHERE mode = ? " +
                "AND id_to = ? " +
                "AND (flags & " +Balance.FLAG_INHERIT_TEMPLATE+ ") <> 0 ",
                new String[]{
                        "" + Balance.MODE_NORMAL,
                        "" + ac.accountID});
        if (cr.moveToFirst()) {
            id = new Long(cr.getLong(0));
        }
        cr.close();

        if(id != -1) {
            return new Balance(this, id);
        } else {
            return null;
        }
    }

    /*
    public void collapse(Calendar at) {

        Calendar to = Main.getPeriod(Calendar.MONTH, at)[0];
        to.add(Calendar.DAY_OF_MONTH, -1);
        to.set(Calendar.HOUR, 23);
        to.set(Calendar.MINUTE, 59);
        to.set(Calendar.SECOND, 0);
        to.set(Calendar.MILLISECOND, 0);

        Balance[] prog = getPrognosis(Main.getPeriod(Calendar.MONTH, at));

        boolean approved = true;
        for (Balance b : prog) {
            if (!b.approved) {
                approved = false;
            }
        }

        if (!approved) {
            //...
        }

        List<Balance> toInsert = new ArrayList<Balance>(0);
        Account[] accounts = getAccountsList();
        for (Account ac : accounts) {
            double[] balance = ac.balanceAt(to);

            if (balance[1] != 0) {
                Balance n = new Balance(this);
                if (ac.type == Account.TYPE_DEBT) {
                    n.accountFrom = ac;
                } else {
                    n.accountTo = ac;
                }
                n.amount = balance[1];
                n.name = context.getResources().getString(R.string.balance_at) + " " + to.getTime().toString();
                n.date = to.getTime();
                toInsert.add(n);
                //n.append();
            }

            if (balance[2] != 0) {
                Balance n = new Balance(this);
                if (ac.type == Account.TYPE_DEBT) {
                    n.accountTo = ac;
                } else {
                    n.accountFrom = ac;
                }
                n.amount = balance[2];
                n.name = context.getResources().getString(R.string.balance_at) + " " + to.getTime().toString();
                n.date = to.getTime();
                toInsert.add(n);
                //n.append();
            }
        }

        List<Balance> toDelete = new ArrayList<Balance>(0);
        Cursor cr = this.getReadableDatabase().rawQuery("SELECT id " +
                "FROM balance " +
                "WHERE mode = ? " +
                "AND date < ?",
                new String[]{"" + Balance.MODE_NORMAL, "" + at.getTimeInMillis()});
        if (cr.moveToFirst()) {
            do {
                toDelete.add(new Balance(this, cr.getLong(0)));
            } while (cr.moveToNext());
        }
        cr.close();

        for (Balance x : toDelete) {
            x.delete();
        }

        for (Balance x : toInsert) {
            x.append();
        }

        DropboxThread.dataChanged();
    }
    */
}


